package com.toppitch.site.controller;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ui.ModelMap;

import com.toppitch.site.data.Paging;
import com.toppitch.site.data.Pitch;
import com.toppitch.site.data.Student;
import com.toppitch.site.db.DBManager;
import com.toppitch.site.db.IGetFromResultSet;

public abstract class UserBaseController extends BaseController {

	@Autowired
	protected DBManager dbmanager;

	protected void home(String page, String count, String searchWords,
			ModelMap model, boolean isStudent, String studentEmail) {
		Paging paging = new Paging();
		paging.setCount(Integer.parseInt(count));
		paging.setCurrentPage(Integer.parseInt(page));
		if (paging.getCurrentPage() <= 0) {
			paging.setCurrentPage(1);
		}

		List<Pitch> pitches = getLatestPitch(paging.getCount(),
				(paging.getCurrentPage() - 1) * paging.getCount(), searchWords,
				paging, isStudent, studentEmail);
		List<Paging> pagings = paging.createPagingList();
		model.addAttribute("pitches", pitches);
		model.addAttribute("page", page);
		model.addAttribute("count", count);
		model.addAttribute("searchWords", searchWords);
		model.addAttribute("pagings", pagings);
		model.addAttribute("needPaging", !pagings.isEmpty());
		model.addAttribute("currentIndex", paging.getCurrentPage());
		model.addAttribute("previousIndex", paging.getCurrentPage() - 1);
		model.addAttribute("nextIndex", paging.getCurrentPage() + 1);
		model.addAttribute("needPrevious",
				!pagings.isEmpty() && paging.getCurrentPage() > 1);
		model.addAttribute(
				"needNext",
				!pagings.isEmpty()
						&& paging.getCurrentPage() < paging.getPageCount());
	}

	protected void mine(String page, String email, ModelMap model) {

		Paging paging = new Paging();
		paging.setCount(10);
		paging.setCurrentPage(Integer.parseInt(page));
		if (paging.getCurrentPage() <= 0) {
			paging.setCurrentPage(1);
		}

		List<Pitch> pitches = getLatestPitchByEmail(paging.getCount(),
				(paging.getCurrentPage() - 1) * paging.getCount(), email,
				paging);
		model.addAttribute("pitches", pitches);

		List<Paging> pagings = paging.createPagingList();
		model.addAttribute("page", page);
		model.addAttribute("pagings", pagings);
		model.addAttribute("needPaging", !pagings.isEmpty());
		model.addAttribute("currentIndex", paging.getCurrentPage());
		model.addAttribute("previousIndex", paging.getCurrentPage() - 1);
		model.addAttribute("nextIndex", paging.getCurrentPage() + 1);
		model.addAttribute("needPrevious",
				!pagings.isEmpty() && paging.getCurrentPage() > 1);
		model.addAttribute(
				"needNext",
				!pagings.isEmpty()
						&& paging.getCurrentPage() < paging.getPageCount());
	}

	protected List<Pitch> getLatestPitch(int count, int from, String keyWord,
			final Paging paging, final boolean isStudent, String studentEmail) {
		final List<Pitch> pitches = new ArrayList<Pitch>();
		String sql = null;
		String sql2 = null;
		String condition = isStudent ? " ideaAvailability='Public' or lower(authorEmail)='"
				+ studentEmail.toLowerCase() + "'"
				: " ideaAvailability<>'Not be seen'";
		if (keyWord == null || keyWord.length() == 0) {
			sql = "select * from ("
					+ "select row_number() over() as rownum, tmp2.* from (select * from pitch where"
					+ condition
					+ " order by posttime desc) as tmp2"
					+ " ) as tmp where rownum >= ? and rownum < ? order by posttime desc";
			sql2 = "select count(*) as totalcount from pitch where" + condition;
		} else {
			String words[] = keyWord.split("\\s+");
			StringBuilder sqlpart = new StringBuilder(" where ");
			for (int i = 0; i < words.length; ++i) {
				if (i != 0) {
					sqlpart.append("and (");
				} else {
					sqlpart.append("(");
				}
				sqlpart.append("companyTicker like '%")
						.append(words[i].replace("'", "''"))
						.append("%' or companyName like '%")
						.append(words[i].replace("'", "''"))
						.append("%' or description like '%")
						.append(words[i].replace("'", "''")).append("%') ");
			}
			sql = "select * from ("
					+ "select row_number() over() as rownum, tmp2.* from (select * from pitch where"
					+ condition
					+ " order by posttime desc) as tmp2"
					+ sqlpart.toString()
					+ " ) as tmp where rownum >= ? and rownum < ? order by posttime desc";
			sql2 = "select count(*) as totalcount from pitch"
					+ sqlpart.toString() + " and" + condition;
		}
		dbmanager.doQuery(new IGetFromResultSet() {
			public void getFromResultSet(ResultSet rs) throws SQLException {
				while (rs.next()) {
					Pitch pitch = new Pitch();
					pitch.getFromResultSet(rs);

					Student student = new Student();
					dbmanager.queryFirst(student,
							"select * from student where lower(email)=?",
							pitch.getAuthorEmail().toLowerCase());
					if (isStudent) {
						pitch.setAuthorAvailable(student.isAvailableToPublic());
					} else {
						pitch.setAuthorAvailable(student.isAvailableToHf());
					}
					pitches.add(pitch);
				}
			}
		}, sql, from, from + count);
		dbmanager.doQuery(new IGetFromResultSet() {
			public void getFromResultSet(ResultSet rs) throws SQLException {
				int total = 0;
				while (rs.next()) {
					total = rs.getInt("totalcount");
				}
				paging.setTotal(total);
			}
		}, sql2);
		return pitches;
	}

	protected List<Pitch> getLatestPitchByEmail(int count, int from,
			String email, final Paging paging) {
		final List<Pitch> pitches = new ArrayList<Pitch>();
		dbmanager
				.doQuery(
						new IGetFromResultSet() {
							public void getFromResultSet(ResultSet rs)
									throws SQLException {
								while (rs.next()) {
									Pitch pitch = new Pitch();
									pitch.getFromResultSet(rs);
									pitches.add(pitch);
								}
							}
						},
						"select * from ("
								+ "select row_number() over() as rownum, tmp2.* from ("
								+ "select * from pitch where lower(authorEmail)=? order by posttime desc) as tmp2"
								+ ") as tmp where rownum >= ? and rownum < ? order by posttime desc",
						email.toLowerCase(), from, from + count);
		dbmanager.doQuery(new IGetFromResultSet() {
			public void getFromResultSet(ResultSet rs) throws SQLException {
				while (rs.next()) {
					paging.setTotal(rs.getInt("totalcount"));
				}
			}
		}, "select count(*) as totalcount from pitch where lower(authorEmail)=?",
				email.toLowerCase());
		return pitches;
	}
}
